package demo2.Mapper;

import demo2.Util.IDBUtil;
import demo2.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Component
public class UserMapperImpl implements IUserMapper {

    private static final String INSERT_SQL =
            "INSERT INTO User (username, password, nickname, age) VALUES (?, ?, ?, ?)";
    private static final String SELECT_SQL =
            "SELECT * FROM User WHERE username = ?";
    private static final String UPDATE_SQL =
            "UPDATE User SET nickname = ?, age = ? WHERE id = ?";
    private static final String DELETE_SQL =
            "DELETE FROM User WHERE id = ?";
    private static final String SELECT_ALL_SQL = "SELECT username, password, nickname, age, id FROM User";

    private static final String UPDATE_2_SQL= "UPDATE User SET password=?,nickname = ?, age = ? WHERE username = ?";

    @Autowired
    private IDBUtil dbUtil;

    @Override
    public void insert(User user) {
        try (Connection conn = dbUtil.getConnection()) {
            conn.setAutoCommit(false);
            if (isUsernameExists(conn, user.getUsername())) {
                conn.rollback();
                throw new DuplicateUsernameException("用户名 '" + user.getUsername() + "' 已存在");
            }
            // 插入新记录
            try (PreparedStatement pstmt = conn.prepareStatement(INSERT_SQL)) {
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getPassword());
                pstmt.setString(3, user.getNickname());
                pstmt.setInt(4, user.getAge());
                pstmt.executeUpdate();
            }
            conn.commit();
        } catch (SQLException e) {
            handleDatabaseError("插入用户失败", e);
        }
    }
    public static class DuplicateUsernameException extends RuntimeException {
        public DuplicateUsernameException(String message) {
            super(message);
        }
    }
    private boolean isUsernameExists(Connection conn, String username) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(SELECT_ALL_SQL)) {
            stmt.setString(1, username);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    @Override
    public User select(String username) {
        try (Connection conn = dbUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_SQL)) {

            pstmt.setString(1, username);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToUser(rs);
                }
            }
        } catch (SQLException e) {
            handleDatabaseError("查询用户失败", e);
        }
        return null;
    }

    @Override
    public void update(User user) {
        try (Connection conn = dbUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(UPDATE_SQL)) {
            pstmt.setString(1, user.getNickname());
            pstmt.setInt(2, user.getAge());
            pstmt.setInt(3, user.getId());

            pstmt.executeUpdate();
        } catch (SQLException e) {
            handleDatabaseError("更新用户失败", e);
        }
    }

    @Override
    public void delete(int userId) {
        try (Connection conn = dbUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(DELETE_SQL)) {

            pstmt.setInt(1, userId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            handleDatabaseError("删除用户失败", e);
        }
    }

    @Override
    public List<User> list() {
        List<User> userList = new ArrayList<>();
        try (Connection conn = dbUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_ALL_SQL);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                User user = mapResultSetToUser(rs);
                userList.add(user);
            }
        }catch (SQLException e){
            handleDatabaseError("查询用户列表失败", e);
        }
        return userList;
    }

    @Override
    public void login(User user) {
        try (Connection conn = dbUtil.getConnection();){
            final String sql="select password from User where username=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String password = rs.getString("password");
                if (password.equals(user.getPassword())) {
                    System.out.println("登录成功！");
                    break;
                }
            }
        }catch (SQLException e){
            handleDatabaseError("用户登录失败", e);
        }
    }

    @Override
    public void update2(User user) {
       try ( Connection conn = dbUtil.getConnection();){
           final String sql="select * from User where username=?";
           PreparedStatement pstmt1 = conn.prepareStatement(sql);
           pstmt1.setString(1, user.getUsername());
           ResultSet rs = pstmt1.executeQuery();
           while (rs.next()) {
               PreparedStatement pstmt = conn.prepareStatement(UPDATE_2_SQL);
               pstmt.setString(4, user.getUsername());
               pstmt.setString(1, user.getPassword());
               pstmt.setString(2, user.getNickname());
               pstmt.setInt(3, user.getAge());
               pstmt.executeUpdate();
               System.out.println("更新成功！");
               break;
           }
           while(!rs.next()){
               System.out.println("用户不存在！");
               break;
           }

       }catch (SQLException e){
           handleDatabaseError("更新用户失败", e);
       }
    }

    // Helper method: Map ResultSet to User object
    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setNickname(rs.getString("nickname"));
        user.setAge(rs.getInt("age"));
        return user;
    }


    private void handleDatabaseError(String message, SQLException e) {
        System.err.println(message + ": " + e.getMessage());
    }
}